﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;
using AG.Utilities;
using System.Data.SqlClient;

namespace SQLServerHealthCheck.SQLServer.AutoFixes
{
    public class DatabaseFixDatabaseSnapshots : AutoFixBase
    {
        public DatabaseFixDatabaseSnapshots()
        {
            IssueTypeId = 3019;
        }

        public override void Fix(List<Issue> issues)
        {
            try
            {

                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());
                ThresholdValue hoursAgo = dc.ThresholdValues.FirstOrDefault(t => t.IssueTypeID == IssueTypeId && t.ThresholdValueID == 2);


                var Qry = (from ii in issues
                           from iss in dc.Issues
                           from ser in dc.Services
                           from i in ser.Databases
                           from s in i.Databases
                           where i.DatabaseID == s.SourceDatabaseId && s.SourceDatabaseId == ii.TargetId
                           && ii.IssueTypeId == IssueTypeId
                           && iss.IssueId == ii.IssueId
                             && s.CreateDate < (DateTime.Now.Date.AddHours((int)(-hoursAgo.Value)))
                           select new { cns = ser.GetConnectionString(i.DatabaseName), databasename = s.DatabaseName, IssueId = ii.IssueId });

                foreach (var item in Qry)
                {
                    string connectionString;
                    connectionString = item.cns;
                    using (SqlConnection cn = new SqlConnection(connectionString))
                    {

                        string query = @"  DROP DATABASE [" + item.databasename + "] ;";
                        SqlCommand cmd = new SqlCommand(query, cn);
                        cmd.Connection.Open();
                        cmd.ExecuteScalar();


                    }
                }

                foreach (var item in issues)
                {
                    Issue issue = dc.Issues.SingleOrDefault(i => i.IssueId == item.IssueId);
                    issue.Fixed = true;

                    dc.SubmitChanges();
                }
            }




            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }





    }
}
